﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlClient;
namespace sr
{
  class ScanReturns
  {
    
    public void ScanFile(string fileName)
    {
      bool headerRow = true;
      List<string> SqlStatements = new List<string>();
      int campaignCol = 0;
      int forenameCol = 0;
      int surnameCol = 0;
      int postcodeCol = 0;
      int reasonCol = 0;
      using (StreamReader sr = File.OpenText(fileName))
      {
        string s = String.Empty;
        while ((s = sr.ReadLine()) != null)
        {
          // s is the current line
          if (headerRow)
          {
            // split the row up into columns
            int colindex=0;
            foreach (var item in s.Split(','))
            {
              switch (item.ToLower())
              {
                case "client":
                  campaignCol = colindex;
                  break;
                case "forename":
                  forenameCol = colindex;
                  break;
                case "firstname":
                  forenameCol = colindex;
                  break;
                case "surname":
                  surnameCol = colindex;
                  break;
                case "lastname":
                  surnameCol = colindex;
                  break;
                case "postcode":
                  postcodeCol = colindex;
                  break;
                case "reason":
                  reasonCol = colindex;
                  break;
                case "forwarding_responses":
                  reasonCol = colindex;
                  break;
                default:
                  break;
              }
              colindex++;
            }
            headerRow = false;
          }
          else
	        {
            string newLine = "";
            bool inQuotes = false;
            foreach (char item in s.ToCharArray())
            {
              if (item == '"')
              {
                inQuotes = !inQuotes;
              }
              else if (item==',' && inQuotes)
              {
                newLine += '-'; 
              }
              else
              {
                newLine += item;
              }
            }
            List<string> leadColumns = newLine.Split(',').ToList();
            string se = "update dbo.exportdatahistory set customerreceived=0,notes='" + leadColumns[reasonCol] + "' " +
              "from dbo.exportdatahistory e " +
              "inner join dbo.purchaseorders p on e.purchaseorderid=p.purchaseorderid and p.visible=1" +
              "inner join dbo.surveys s on p.surveyid=s.surveyid and s.shortref='" + leadColumns[campaignCol] + "' " +
              "where datatext like '%" + leadColumns[forenameCol] + "%" + leadColumns[surnameCol] + "%" + leadColumns[postcodeCol] + "%' ";
            SqlStatements.Add(se);
	        }
        }
      }
      using (SqlConnection conn = new SqlConnection())
      {
        conn.ConnectionString = "Data Source=webellas.arvixevps.com;Initial Catalog=oracsurveys;Integrated Security=false;User ID=orac;Password=20hamlet";
        conn.Open();
        // using the code here...
        SqlCommand command = new SqlCommand();
        command.Connection = conn;
        foreach (string item in SqlStatements)
        {
          command.CommandText = item;
          command.ExecuteNonQuery();
        }
        conn.Close();
      }
    }
  }
}
